package laponderosa.whereismyembassy;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataBaseHelper extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "embajadas";

	// Contacts table name
	private static final String TABLE_CONTACTS = "embajadas";

	// Contacts Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_NAME = "name";
	private static final String KEY_PH_NO = "phone_number";

	Context contexto;

	public DataBaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		contexto = context;
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		/**
		 * String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
		 * + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT," + KEY_PH_NO
		 * + " TEXT" + ")"; db.execSQL(C REATE_CONTACTS_TABLE);
		 **/

		String crea = "create table embajadas (_id INTEGER PRIMARY KEY, pais TEXT, direccion TEXT, telefono TEXT, web TEXT, email TEXT, longitud TEXT, latitud TEXT)";
		
		//Log.d("BRAULIO",crea);
		
		db.execSQL(crea);
		
		
		InputStream inputStream = contexto.getResources().openRawResource(
				R.raw.consulados);

		InputStreamReader inputreader = new InputStreamReader(inputStream);
		BufferedReader buffreader = new BufferedReader(inputreader);
		String[] line;
		String linea;
		String text = String.valueOf( Character.toChars(34) );
		//db = this.getWritableDatabase();
		try {
			
			while ((linea = buffreader.readLine()) != null) {
				linea = linea.replace(text,"");
				linea = linea.replace(","," ");
				line = linea.split(";");
				 String inserta = "insert into embajadas (_id, pais, direccion,telefono,web,email,longitud,latitud) values ("+line[0]+" ,'"+line[1]+"','"+line[4]+" "+line[5]+ " "+line[3]+"','"+line[6]+"','"+line[7]+"' ,'"+line[8]+"' ,'"+line[9]+"' ,'"+line[10]+"' )";
				 db.execSQL(inserta);
				
				
			}
			 // Closing database connection
			
		} catch (IOException e) {
			// Log.i("BRAULIO RULES", e.getMessage());
		}
		//dbI.close();
		
		
		
		
		
		
		
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

		// Create tables again
		onCreate(db);
	}

	
	
	public List<String> paisesLista() {
        List<String> contactList = new ArrayList<String>();
        // Select All Query
        String selectQuery = "SELECT  pais FROM embajadas order by pais";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
 
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                contactList.add(cursor.getString(0));
            } while (cursor.moveToNext());
        }
 
        db.close();
        // return contact list
        cursor.close();
        cursor = null;
        return contactList;
    }
	
	
	public embajada paisDatos(String pais) {
        
        // Select All Query
        String selectQuery = "SELECT * FROM embajadas where pais ='"+pais+"'";
        embajada resultado;
        resultado = null;
        
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = db.rawQuery(selectQuery, null);
 
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
            	//_id INTEGER PRIMARY KEY, pais TEXT, direccion TEXT, telefono TEXT, web TEXT, email TEXT, longitud TEXT, latitud TEXT
                resultado =new embajada();
                resultado.setDireccion(cursor.getString(2));
                resultado.setEmail(cursor.getString(5));
                resultado.setPais(cursor.getString(1));
                resultado.setTelefono(cursor.getString(3));
                resultado.setLongitud(cursor.getString(6));
                resultado.setLatitud(cursor.getString(7));
            } while (cursor.moveToNext());
        }
 
        db.close();
        // return contact list
        cursor.close();
        cursor = null;
        return resultado;
    }
    
    
 

}